<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_main extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $sqlSelect =" select  * from tb_pos p Where 1=1 " ;
	private $totalpage="0";
	
	function M_main(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}
	function getTransaction($posid){
		$sqlh= "SELECT b.BranchName, p.ID, p.Amount , p.Discount
				, p.TotalNet , DATE_FORMAT(p.SaleDate , '%d/%m/%Y %H:%i:%s') as SaleDate
				, DATE_FORMAT(p.SaleDate , '%y%m%d') SaleDate2 , b.BranchCode ,
				IsExtraDiscount , ExID,Tax, b.Address, p.CustID, c.FirstName, c.LastName ,p.BillNo
				From tb_pos p
				  JOIN tb_branch b on b.ID=p.Branch
				  left JOIN tb_customer c on c.ID=p.CustID
				Where  b.IsActived=1 and p.ID=".$posid;
		$result = $this->ExecuteData($sqlh);

		$header=$result->FetchRow();
		 // Detail
		$sql=" SELECT pd.ProductName, d.Amount , d.Total
				From tb_pos p
				JOIN tb_pos_detail d on d.Pos_id=p.ID
				JOIN tb_product pd on pd.ID=d.Product
				Where p.ID=".$posid;
		$result = $this->ExecuteData($sql);
		$ar_result= Array();
		while($row=$result->FetchRow()){
			array_push($ar_result, $row);
		}
		//Payment
		$sql=" SELECT py.PaymentType , py.PaymentAmount
				From tb_pos p
				JOIN tb_pos_payment py on py.Pos_id=p.ID
				Where p.ID=".$posid;
		$result = $this->ExecuteData($sql);
		$ar_pay= Array();
		while($row=$result->FetchRow()){
			array_push($ar_pay, $row);
		}
		
		if ($header['IsExtraDiscount'] <> 0){
			$sql=" SELECT Amount
				, case when Type=1 then (select ProductName from tb_product where ID=e.Discount)
					Else Discount End DiscountType
				, e.Type
				From tb_extra_discount e
				Where e.ID=".$header['ExID'];
			 
			$discount = $this->ExecuteData($sql);		
			$re_dist=$discount->FetchRow();
			$header['specailDiscount']=$re_dist;
		}
		if ($header['CustID']!='0'){
			$header['course']=$this->getCreditSaleRemain($header['CustID']);
		}
		$header['detail']=$ar_result;
		$header['payment']=$ar_pay;
 
		return $header;//$ar_result;			
	}
	function checkRemainAmt($id, $bid ,$amt, $type){
		if ($type==1) {
			$sql = "  SELECT p.ProductName, MainAmt as  Amount ,u.Unit
				 FROM tb_stock_product t
				 JOIN tb_product p on p.ID=t.ProductID
				 left join tb_units u on u.ID=p.MainUnit
				 where p.IsDeleted=0 and t.ProductID =".$id." and t.BranchID =".$bid;
				 
			$result = $this->ExecuteData($sql);
			$row=$result->FetchRow();
				if ($amt > $row['Amount'] ){
					$ar = array('name'=>$row['ProductName'],
								'amt'=>number_format($row['Amount'],0),
								'unit'=>$row['Unit'],
								'result'=>0
								);
					
				}else{
					$ar = array('name'=>$row['ProductName'],
								'amt'=> number_format($row['Amount'],0),
								'unit'=>$row['Unit'],
								'result'=>1
								);
				}
		}
		else if ($type==2 || $type==3) {
			$sql = "  select d.SubProductID  ,ps.ProductName  , s.Amount as RemainAmt , s.MainAmt as RemainMain ,d.Amount  
					, (SELECT u.Unit FROM tb_units u where U.ID = (Case when ps.SubUnit = '' or ps.SubUnit is null then ps.MainUnit else ps.SubUnit end)) Unit
						from tb_product p
						left join tb_product_details d on d.ProductID=p.ID
						left join tb_stock_product s on s.ProductID = d.SubProductID
						left join tb_product ps on ps.ID = d.SubProductID
						where p.IsDeleted=0 and p.ID=".$id. " and s.BranchID =".$bid;
						
			$result = $this->ExecuteData($sql);
			while( $row=$result->FetchRow()){
				if (($row['Amount']*$amt) > $row['RemainAmt']){
						$ar = array('name'=>$row['ProductName'],
							'amt'=> number_format($row['RemainAmt'],0),
							'unit'=>$row['Unit'],
							'result'=>0
							);
						 break;
				} else {
					$ar = array(
						'result'=>1
						);
				}
			}
			
						
		}else if ($type==4){
			$sql = "select pp.ID, pp.ProductName, MIN(s.Amount) RemainAmt, MIN(s.MainAmt) RemainMain
				, SUM(ds.Amount) Amount
				, (SELECT u.Unit FROM tb_units u where U.ID = (Case when pp.SubUnit = '' or pp.SubUnit is null then pp.MainUnit else pp.SubUnit end)) Unit
				from tb_product p
				left JOin tb_product_details d on d.ProductID=p.ID
				left join tb_product ps on ps.ID = d.SubProductID
				left join tb_product_details ds on ds.ProductID=ps.ID
				left join tb_stock_product s on s.ProductID = ds.SubProductID
				left join tb_product pp on pp.ID=s.ProductID
				where p.IsDeleted=0 and p.ID=".$id. " and s.BranchID = ".$bid." Group by pp.ID, pp.ProductName	";
				
			$result = $this->ExecuteData($sql);
			while( $row=$result->FetchRow()){
				if (($row['Amount']*$amt) > $row['RemainAmt']){
					$ar = array('name'=>$row['ProductName'],
							'amt'=> number_format($row['RemainAmt']),
							'unit'=>$row['Unit'],
							'result'=>0
							);
						 break;
				} else {
					$ar = array(
						'result'=>1
						);
				}
			}				
		}
		return $ar;
	}

	function getCreditSaleRemain($custid){
		$sql = " select coalesce(SUM(RemainAmt) ,0) as RemainAmt from tb_creditsale where IsDeleted = 0  and CustID = ".$custid;

		$result = $this->ExecuteData($sql);
		$row1=$result->FetchRow();

		$sql = " select P.ProductName , cs.Remain ,  DATE_FORMAT(ExpireDate , '%d/%m/%Y') as ExpireDate
				from tb_customer_service  cs
				join tb_product P on P.ID=cs.ProductID
				where cs.IsCancel = 0 and Remain > 0 and ExpireDate >= NOW()  and CustID =". $custid;

		$result = $this->ExecuteData($sql);
		$sRowsCount =$result->RecordCount();
		
		$ServiceResult=array();

		while($row2=$result->FetchRow()){
			array_push($ServiceResult,$row2);
		}

		$arResult = array('remainAmt'=>$row1['RemainAmt'],
						'serviceCnt'=>$sRowsCount,
						'servicelst'=>$ServiceResult
					);	

		return $arResult;

	}
	function checkSpecialDiscount(){
		$sql = " select e.ID, e.Amount, e.Description , e.Type , e.Discount  "
			." from tb_extra_discount e where  IsDeleted=0 and Now() between StartDate and EndDate  Order by e.Amount asc ";
		
		$result = $this->ExecuteData($sql);
		$ar_result= Array();
		while($row=$result->FetchRow()){
			array_push($ar_result, $row);
		}
		return $ar_result;
	}
	function getHistoryData( $branchID="0" ,$pageStart=1,$datefrom=null , $dateto=null){
	 global $list_page;
		$sqlscript = 'SELECT DATE_FORMAT(SaleDate , \'%d/%m/%Y\') SaleDate1   , DATE_FORMAT(SaleDate , \'%Y/%m/%d\') SaleDate2 '
        . ' , sum(Amount)  Amount, sum(Discount) Discount, Sum(TotalNet) as Total, sum(PaymentAmount)  Payment, sum(RemainAmount) Remain '
		. ' , p.Branch , b.BranchName , IsCancel as flag  '
        . ' FROM tb_pos p '
		.'  join tb_branch b on b.ID=p.Branch ' 
        . ' WHERE 1=1 and p.IsCancel = 0  ';
		
		if ($branchID!="0"){
			$sqlscript.= ' and p.Branch='.$branchID;
		}
		
		if (isset($datefrom) && $datefrom!=""){
			$sqlscript.= " and DATE_FORMAT(p.SaleDate,'%Y/%m/%d') Between '". $datefrom."' and '".$dateto."'";
		}else{
			$sqlscript.= " and DATE_FORMAT(p.SaleDate,'%Y/%m/%d') Between DATE_ADD(CURDATE(), INTERVAL  - 1 day) and CURDATE() ";
		}
		$sqlscript .= ' group by DATE_FORMAT(SaleDate , \'%d/%m/%Y\'), p.Branch '; 
		//Order 
		$sqlscript.= " order by  p.Branch ,p.SaleDate Desc " ;	
		//Execute
		//	 print_r( $sqlscript );
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);			
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;		
	}
	function getPaymentHistory($custid=0, $branchId=null,  $date=null){
		$sql=" select CASE WHEN p.PaymentType=1 THEN 'Cash' When p.PaymentType=2 THEN 'Credit Card' Else 'Smart Purse' END PayType ,   "
			." Sum(p.PaymentAmount) PaymentAmount "
			." from tb_pos po  "
			. " join tb_pos_payment p on po.ID=p.Pos_id "
			. " where DATE_FORMAT(po.SaleDate,'%Y/%m/%d') = '" .$date . "'"
			. " and IsCancel=0 and po.CustID=".$custid." and Branch=".$branchId
			. " group by p.PaymentType ";
		$result = $this->ExecuteData($sql);	
		$arr = array();
		while ($row = $result->FetchRow()){
			array_push($arr,$row);
		}
		return  $arr ;
	}
	function getHistoryDetail($branchId=null,  $date=null, $type="customer" , $pageStart=1){
	 global $list_page;
		if ($type=="customer"){
			$sqlscript=" select p.CustID, c.FirstName, c.LastName  , sum(p.Amount)  as Amount, sum(p.Discount) Discount "
						." , sum(p.PaymentAmount) as Payment , sum(p.TotalNet) TotalNet "
						." , sum(p.RemainAmount ) as Remain"
						. " from tb_pos p "
						. " Left join tb_customer c on c.ID= p.CustID "
						. " where DATE_FORMAT(p.SaleDate,'%Y/%m/%d') = '" .$date . "'"
						. " and IsCancel=0 and Branch=".$branchId 
						. " group by  c.FirstName , c.LastName,p.CustID "
						. " order by sum(p.Amount) desc ";
		}else{
			$sqlscript=" select  pd.ProductName,  t.Type as ProductType , u.Unit  "
					." , sum(d.Amount) Amount , Max(d.Price)  Price, sum(d.Discount) Discount , sum( (d.Amount * d.Price) - d.Discount)  total "
					. " from tb_pos p "					
					. " join tb_pos_detail d on p.ID=d.Pos_id "
					. " join tb_product pd on d.product = pd.ID " 
					. " join tb_units u on u.id = pd.MainUnit " 
					. " left join tb_product_type t on t.ID=pd.Type "
					. " where DATE_FORMAT(p.SaleDate,'%Y/%m/%d') = '" .$date . "'"
					. " and p.IsCancel=0 and Branch=".$branchId 
					. " group by pd.ProductName, t.Type ,u.Unit"
					. " order by pd.ProductName asc ";
		}
		//Execute
	//return $sqlscript;
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);			
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;				
	}
	
	function Excute($sql){
		$this->ExecuteData($sql);
	}
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	function getPremiumID($id){
		$sql=" SELECT e.Discount as PID
		  FROM tb_extra_discount e
		  where e.Type=1 and e.ID=".$id;
		$result=$this->ExecuteData($sql);
		$rows=$result->FetchRow();
		return $rows['PID'];
	}
	function InsertData($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript($this->sqlSelect, $val);
			//$this->ExecuteDataAndGetID($sqlInsert);
			$this->ID = $this->ExecuteDataAndGetID($sqlInsert);
			return  $this->ID;
		}
	}
	function InsertService( $posid, $custid, $pid , $branchid, $total, $times ,$user ){
		$sql=" insert into tb_customer_service (CustID, POSID, ProductID, BranchID_Sale , Prices ,Times ,Remain , CreatedDate ,CreatedBy, ExpireDate) ";
		$sql.=" values(".$custid.",".$posid.",".$pid.",".$branchid.",".$total.",".$times.",".$times.",Now(),'".$user."', DATE_ADD(NOW(),INTERVAL 1 YEAR)) ";

		$this->ExecuteData($sql);
		return $sql;
		
	}
	
	function getLastBillNo($bid){
		$sql ="select BillNo from tb_branch where ID='".$bid."'";
		
		$result = $this->ExecuteData($sql);
		
		$sqlupdate="update tb_branch set BillNo=BillNo+1 where ID='".$bid."'";
		$this->ExecuteData($sqlupdate);
		
		$row=$result->FetchRow();
		
		return $row["BillNo"]+1;
	}
	function InsertCreditSale($val,$pos,$user,$payType,$cardNo){
		$ar=array('CustID'=>$val['CustID']
						,'Buydate'=>date( "Y/m/d H:i:s" )
						,'LastPay'=>date( "Y/m/d H:i:s" )
						,'TotalAmt'=>$val['TotalNet']
						,'PayAmt'=>$val['PaymentAmount']
						,'RemainAmt'=>$val['RemainAmount']
						,'PosID'=>$pos
						,'CreatedDate'=>date( "Y/m/d H:i:s" )
						,'CreatedBy'=>$user
				);
		$sqlInsert=$this->InsertSqlScript("select * from tb_creditsale where 1=2 ", $ar);
		$this->ExecuteData($sqlInsert);
		
		$credit_id=$this->getLastID();
		
		$arDetail = array('Credit_ID'=>$credit_id
								,'PayDate'=>date( "Y/m/d H:i:s" )
								,'Amount'=>$val['PaymentAmount']
								,'PayType'=>$payType
								,'CardNo'=>$cardNo
								,'CreatedBy'=>$user
								);
								
		$sqlInsertDetail=$this->InsertSqlScript("select * from tb_creditsale_detail where 1=2 ", $arDetail);
		$this->ExecuteData($sqlInsertDetail);
		
		return $sqlInsert. ' ' .$sqlInsertDetail ; //$credit_id;
		//$sqlInsert = "Insert into tb_creditsale ( CustID, Buydate, LastPay, TotalAmt, PayAmt, RemainAmt , pos, Cre)  values 
	}
	
	function updateLastAccess($id){
		$sqlUpdate="update tb_customer set LastedAccess=Now() where ID=".$id;
		$this->ExecuteData($sqlUpdate);
	}
	function updateData($sql, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript($sql,$val);
			return $this->ExecuteData($sqlUpdate);
		}
	}
	function delData($id){
		if(isset($id)){
			$sqlDelete = "delete from tb_receive_product where ID='".$id."'";
			$this->ExecuteData($sqlDelete);
			//delete detail
			$this->delDataDetail($id);
			return "c";
		}
	}

	function getTotalPage(){
		return $this->totalpage;
	}

}
?>